using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Reflection;

using MySql.Data.MySqlClient;
using Oracle.DataAccess.Client;

namespace runner
{
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	public class MigrationRunner
	{
		private IDbConnection _connection;
		private IDbTransaction _transaction;
		private IDbCommand _command;
		private SortedList _migrations = new SortedList();
		private MigrationChangeList _changelist = new MigrationChangeList();
		private string _migrationpath;
		private const string SCHEMA_VERSION_SQL = "SELECT version FROM SCHEMA_VERSION";
		private int _currentversion = Constants.BASE_VERSION;
		private int _rollbackversion = Constants.BASE_VERSION;

		public MigrationRunner(IDbConnection connection, string migration_path)
		{
			_connection = connection;
			_command = _connection.CreateCommand();
			_command.Connection = _connection;
				
			_migrationpath = migration_path;

			ReadMigrations();
		}

		public int Execute() 
		{
			return Execute(Constants.UNSPECIFIED_INTEGER);
		}

		public int Execute(int version) 
		{
			try 
			{
				if (_connection.State == ConnectionState.Closed) 
				{
					_connection.Open();
				}

				_transaction = _connection.BeginTransaction(IsolationLevel.Serializable);
				_command.Transaction = _transaction;

				// SET VERSION IF MISSING OR UNSPECIFIED
				if (version == Constants.UNSPECIFIED_INTEGER) 
				{
					version = ReadLargestVersion();
				}

				_rollbackversion = QueryVersion();
				_currentversion = _rollbackversion;

				Migrate(version);

				_transaction.Commit();
			}
			catch (Exception except) 
			{
				System.Console.Out.WriteLine(except);
				try 
				{
					System.Console.Out.WriteLine("begin rollback version: " + _rollbackversion);

					_transaction.Rollback();
					_currentversion = _rollbackversion;

					System.Console.Out.WriteLine("end rollback version: " + _rollbackversion);
				}
				catch (Exception transaction_except) 
				{
					System.Console.Out.WriteLine(transaction_except);
				}
			}
			finally 
			{
				_connection.Close();
			}

			return _currentversion;
		}

		private void Migrate(int version) 
		{
			if (version == _currentversion) 
			{
				System.Console.Out.WriteLine("version (" + version + ") is the current version");
			}
			else if (version >= _currentversion)
			{
				for(int c = _currentversion + 1; c <= version; ++c) 
				{
					if (_migrations.ContainsKey(c)) 
					{
						Migration migration = ((Migration)_migrations[c]);
						migration.PreviousVersion = _currentversion;
						_changelist.Add(migration);

						migration.AddComments("begin migrating from (" + _currentversion + ") to (" + version + ")");

						_command.CommandText = migration.Up;
						int result = (int)_command.ExecuteNonQuery();

						if (result != -1) 
						{
							_currentversion = migration.Version;

							_command.CommandText = "UPDATE SCHEMA_VERSION SET version = " + _currentversion;
							if (_command.ExecuteNonQuery() == -1) 
							{
								migration.AddError("Could not update the SCHEMA_VERSION table");
								throw new Exception(migration.Error);
							}
						}
						else
						{
							migration.AddError("Sql returned an improper result value");
							throw new Exception(migration.Error + ": " + migration.Up);
						}
					}
				}
			}
			else 
			{
				for(int c = _currentversion; c > version; --c) 
				{
					if (_migrations.ContainsKey(c)) 
					{
						Migration migration = ((Migration)_migrations[c]);
						migration.PreviousVersion = _currentversion;
						_changelist.Add(migration);

						migration.AddComments("begin migrating from (" + _currentversion + ") to (" + version + ")");

						_command.CommandText = migration.Down;
						int result = (int)_command.ExecuteNonQuery();

						if (result != -1) 
						{
							_currentversion = migration.Version;

							int previousversion = ReadPreviousVersion();
							_command.CommandText = "UPDATE SCHEMA_VERSION SET version = " + previousversion;
							_currentversion = previousversion;

							if (_command.ExecuteNonQuery() == -1) 
							{
								migration.AddError("Could not update the SCHEMA_VERSION table");
								throw new Exception(migration.Error);
							}
						}
						else
						{
							migration.AddError("Sql returned an improper result value");
							throw new Exception(migration.Error + ": " + migration.Down);
						}
					}
				}
			}
		}

		public int QueryVersion() 
		{
			if (_connection.State == ConnectionState.Closed) 
			{
				_connection.Open();
			}

			_command.CommandText = SCHEMA_VERSION_SQL;

			try 
			{
				_currentversion = (int)_command.ExecuteScalar();
			}
			catch (MySqlException) 
			{
				Migration migration = (Migration)_migrations[0];
				migration.PreviousVersion = -1;
				_command.CommandText = migration.Up;
				_currentversion = (int)_command.ExecuteScalar();
			}
			catch (OracleException) 
			{
				Migration migration = (Migration)_migrations[0];
				migration.PreviousVersion = -1;
				_command.CommandText = migration.Up;

				object obj = _command.ExecuteScalar();
				_currentversion = (int)obj;
			}
			catch (Exception except)
			{
				System.Console.Out.WriteLine(except);
				throw except;
			}

			return _currentversion;
		}

		public SortedList Migrations 
		{
			get { return _migrations; }
		}
		
		public MigrationChangeList MigrationChangeList
		{
			get { return _changelist; }
		}

		private int ReadLargestVersion() 
		{
			int largest_version = 0;

			IEnumerator iter = _migrations.Keys.GetEnumerator();
			while(iter.MoveNext()) 
			{
				int version = (int)iter.Current;
				if (largest_version < version) 
				{
					largest_version = version;
				}
			}
			
			return largest_version;
		}

		private int ReadPreviousVersion() 
		{
			return ((Migration)_migrations[_currentversion - 1]).Version;
		}

		private void ReadMigrations() 
		{
			Migration schema_migration = new Migration(0, "schema_migration_creation");

			try 
			{
				MySqlConnection try1 = (MySqlConnection)_connection;
				schema_migration.Up = "CREATE TABLE SCHEMA_VERSION(version int NOT NULL); INSERT INTO SCHEMA_VERSION(version) VALUES(0); SELECT version FROM SCHEMA_VERSION;";
				schema_migration.Down = "DROP TABLE SCHEMA_VERSION;";
			}
			catch (Exception) 
			{
				try 
				{
					OracleConnection try2 = (OracleConnection)_connection;
					schema_migration.Up = "BEGIN execute immediate 'CREATE TABLE SCHEMA_VERSION(version int NOT NULL)'; execute immediate 'INSERT INTO SCHEMA_VERSION(version) VALUES(0)'; execute immediate 'SELECT version FROM SCHEMA_VERSION'; commit; END;";
					schema_migration.Down = "DROP TABLE SCHEMA_VERSION;";
				}
				catch (Exception)
				{
					schema_migration.AddError("did not recognize connection type: " + _connection.GetType().Name);
					throw new Exception(schema_migration.Error);
				}
			}

			_migrations.Add(0, schema_migration);

			if (Directory.Exists(_migrationpath)) 
			{
				string[] up_paths = Directory.GetFiles(_migrationpath, "*.up");

				foreach (string path in up_paths) 
				{
					string[] migration_detail = Parse(path);
					if (migration_detail != null)
					{
						try 
						{
							FileStream fs = new FileStream(path, FileMode.Open);
							TextReader reader = new StreamReader(fs);

							Migration migration = new Migration(
								int.Parse(migration_detail[0]),
								migration_detail[1]);
							migration.Up = reader.ReadToEnd();

							_migrations.Add(migration.Version, migration);
						}
						catch (Exception) 
						{
						}
					}
				}

				string[] down_paths = Directory.GetFiles(_migrationpath, "*.down");

				foreach (string path in down_paths) 
				{
					string[] migration_detail = Parse(path);
					if (migration_detail != null)
					{
						try 
						{
							FileStream fs = new FileStream(path, FileMode.Open);
							TextReader reader = new StreamReader(fs);

							int migration_version = int.Parse(migration_detail[0]);
							if (_migrations.ContainsKey(migration_version)) 
							{
								Migration migration = (Migration)_migrations[migration_version];
								migration.Down = reader.ReadToEnd();
							}
							else 
							{
								Migration migration = new Migration(
									migration_version,
									migration_detail[1]);
								migration.Up = reader.ReadToEnd();

								_migrations.Add(migration.Version, migration);
							}
						}
						catch (Exception) 
						{
						}
					}
				}
			}
		}

		private string[] Parse(string path) 
		{
			FileInfo fi = new FileInfo(path);

			string[] migration_detail = new string[2];
			
			try 
			{
				int first_delimiter = fi.Name.IndexOf("_");

				if (fi.Name.Length > first_delimiter) 
				{
					migration_detail[0] = fi.Name.Substring(0, first_delimiter);
					migration_detail[1] = fi.Name.Substring(first_delimiter + 1, 
						fi.Name.LastIndexOf(fi.Extension) - (first_delimiter + 1));
				}
			}
			catch (Exception) 
			{
			}
			finally
			{
				fi = null;
			}

			return migration_detail;
		}
	}
}
